# -*- coding: utf-8 -*-

# Podboy -- A podcast aggregator/player
#
# Copyright (C) 2009-2012 Valéry Febvre <vfebvre@easter-eggs.com>
# http://code.google.com/p/podboy/
#
# This file is part of Podboy.
#
# Podboy is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# Podboy is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import os, time
import sqlite3

from const import *

db = None

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


class PodboyDB(object):
    def __init__(self):
        cfg_path = os.path.join(os.path.expanduser("~"), ".config/podboy")
        if not os.path.exists(cfg_path):
            os.makedirs(cfg_path)
        db_path = os.path.join(cfg_path, 'podboy.db')
        first_run = not os.path.exists(db_path)

        self.cnx = sqlite3.connect(db_path)
        self.cnx.row_factory = dict_factory

        cursor = self.cnx.cursor()
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS podcasts (
                id           INTEGER PRIMARY KEY,
                link         TEXT,
                title        TEXT,
                description  TEXT,
                cover_link   TEXT,
                website_link TEXT,
                etag         TEXT,
                modified     REAL,
                updated      REAL,
                path         TEXT
            );
            """)
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS episodes (
                id          INTEGER PRIMARY KEY,
                podcast_id  INTEGER,
                guid        TEXT,
                link        TEXT,
                title       TEXT,
                description TEXT,
                duration    TEXT,
                length      INTEGER,
                updated     REAL,
                path        TEXT,
                downloaded  INTEGER DEFAULT 0,
                played      INTEGER DEFAULT 0,
                locked      INTEGER DEFAULT 0,
                last_pos    INTEGER DEFAULT 0
            );
            """)
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS settings (
                name  TEXT,
                value TEXT,
                type  TEXT
            );
            """)

        # DB upgrades
        if not first_run:
            version = self.get_setting('version')
            if version is None:
                # add a new field 'path' in table 'podcasts'
                cursor.executescript("""
                    ALTER TABLE podcasts ADD COLUMN path TEXT;
                    """)
                sql = 'UPDATE podcasts SET path = ? WHERE id = ?'
                for podcast in self.get_podcasts():
                    cursor.execute(sql, (podcast['title'], podcast['id']))
                self.cnx.commit()
            if version in (None, '1.4.0'):
                # add a new field 'locked' in table 'episodes'
                cursor.executescript("""
                    ALTER TABLE episodes ADD COLUMN locked INTEGER DEFAULT 0;
                    """)
                self.cnx.commit()

        self.save_setting('version', APP_VERSION)

    def count_episodes(self, podcast_id = None, downloaded = None, played = None):
        sql = """
            SELECT count(*) AS nb FROM episodes
        """

        clauses = []
        values  = []
        if podcast_id is not None:
            clauses.append('podcast_id = ?')
            values.append(podcast_id)
        if downloaded is not None:
            #clauses.append('downloaded = ?' if downloaded else 'downloaded <= ?')
            clauses.append('downloaded = ?')
            values.append(downloaded)
        if played is not None:
            clauses.append('played = ?')
            values.append(played)
        if clauses:
            sql = '%s WHERE %s' % (sql, ' AND '.join(clauses))

        return self.cnx.cursor().execute(sql, values).fetchone()['nb']

    def count_podcasts(self):
        sql = "SELECT count(*) AS nb FROM podcasts"
        return self.cnx.cursor().execute(sql).fetchone()['nb']

    def get_episodes(self, podcast_id = None, downloaded = None):
        sql = """
            SELECT * FROM episodes
        """

        clauses = []
        values  = []
        if podcast_id is not None:
            clauses.append('podcast_id = ?')
            values.append(podcast_id)
        if downloaded is not None:
            clauses.append('downloaded = ?' if downloaded else 'downloaded <= ?')
            values.append(downloaded)
        if clauses:
            sql = '%s WHERE %s' % (sql, ' AND '.join(clauses))
        sql = '%s ORDER BY updated DESC' % sql

        return self.cnx.cursor().execute(sql, values).fetchall()

    def get_old_episodes(self, age):
        sql = """
            SELECT * FROM episodes WHERE played = 1 AND locked = 0 AND updated < ?
        """
        t = time.time() - 86400 * age

        return self.cnx.cursor().execute(sql, (t,)).fetchall()

    def get_podcasts(self):
        sql = """
            SELECT * FROM podcasts
        """
        return self.cnx.cursor().execute(sql).fetchall()

    def get_setting(self, name):
        sql = 'SELECT value, type FROM settings WHERE name = ?'
        row = self.cnx.cursor().execute(sql, (name,)).fetchone()
        if not row:
            return None
        if row['type'] == 'bool':
            return bool(int(row['value']))
        elif row['type'] == 'float':
            return float(row['value'])
        elif row['type'] == 'int':
            return int(row['value'])
        else:
            return row['value']

    def save_setting(self, name, value, data_type = None):
        if self.get_setting(name) is not None:
            sql = "UPDATE settings SET value = ?, type = ? WHERE name = ?"
            self.cnx.cursor().execute(sql, (value, data_type, name))
        else:
            sql = "INSERT INTO settings (name, value, type) VALUES (?, ?, ?)"
            self.cnx.cursor().execute(sql, (name, value, data_type))
        self.cnx.commit()

    def close(self):
        self.cnx.close()

db = PodboyDB()
